package cn.skyisazure.metacompare.other;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.ObjUtil;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.handler.HandleHelper;
import cn.hutool.db.sql.SqlExecutor;
import cn.hutool.json.JSONUtil;
import cn.skyisazure.metacompare.sqlcompare.exceptions.MetaCompareException;
import com.baomidou.dynamic.datasource.creator.DataSourceProperty;
import com.baomidou.dynamic.datasource.creator.DefaultDataSourceCreator;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.transaction.support.TransactionSynchronizationManager;

import javax.sql.DataSource;
import java.io.Closeable;
import java.io.IOException;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.function.Consumer;
import java.util.function.Function;

/**
 * jdbc工具类（！ 使用时注意：需要对Executor进行流关闭）
 *
 * <blockquote><pre>
 *     try(JDBCUtil.Executor executor = jdbcUtil.setDataSourceWithExecutor(PoolNameUtil.composeDSName(metaDataSourceDO.getCode(), metaDataSourceDO.getQueryKey(), metaDataSourceDO.getEnv()));){
 *         executor.execute(sqlModel);
 *     }catch (IOException e){
 *         log.error("数据库连接关闭失败",e);
 * 		   throw new ServiceException("数据库连接关闭失败");
 *     }
 * </pre></blockquote>
 *
 * @author wangjj
 * @date 2023/7/12 11:10
 */
@Slf4j
@RequiredArgsConstructor
@Component
public class JDBCUtil {

    private static final String MASTER_POOL_NAME = "master";
    private final DefaultDataSourceCreator dataSourceCreator;
    private final DynamicDatasourceUtil dynamicDatasourceUtil;



    /**
     * 手动设置数据源信息。临时执行sql（不会注入到多数据源管理）
     * dbinfo密码需解密的密码
     */
    public <R> R executeTempWithClose(DbInfo dbInfo, Function<Executor,R> function) {
        SQLDialectEnum sqlDialectEnum = SQLDialectEnum.Default();
        DataSourceProperty dataSourceProperty = new DataSourceProperty();
        dataSourceProperty.setUrl(dbInfo.getUrl(sqlDialectEnum));
        dataSourceProperty.setUsername(dbInfo.getDatabaseUserName());
        dataSourceProperty.setPassword(dbInfo.getDatabasePassword());
        dataSourceProperty.setDriverClassName(sqlDialectEnum.getDriveClassName());
        try(Executor executor = new Executor(RandomUtil.randomString(6),dataSourceCreator.createDataSource(dataSourceProperty));){
            return function.apply(executor);
        }catch (IOException e){
            log.error("数据库连接关闭失败",e);
            throw new MetaCompareException("数据库连接关闭失败");
        }
    }
    /**
     * 获取执行器【内部使用】
     * @param poolName 数据源名
     * @return sql执行器
     */
    private Executor setDataSourceWithExecutor(String poolName) {
        Map<String, DataSource> dataSources = dynamicDatasourceUtil.getDataSources();
        if (!dataSources.containsKey(poolName)){
            DataSourceProperty dataSourceProperty;
            // 懒加载数据源实现,根据poolName找到相应数据源信息
            dataSourceProperty = new DataSourceProperty();

            Map<String, Serializable> map = PoolNameUtil.apartDSName(poolName);
            Long appId = (Long) map.get("appId");
            String code = (String) map.get("code");
            String key = (String) map.get("key");
            String envStr = (String) map.get("envStr");

            DbInfo dbInfo;
            LinkedHashMap<String,Object> dBInfoMap;
            // 在master库中查询对应的数据源连接信息：
            String sql = "SELECT database_host,database_port,database_name,database_user_name,database_password " +
                    "FROM `meta_data_source` WHERE `app_id`=? AND `code`=? AND query_key=? AND env = ? AND latest=true AND deleted='0'";
            dBInfoMap = this.executeWithClose(executor -> executor.queryOneData(new SqlModel(sql, Arrays.asList("参数列表"))));
            dbInfo = BeanUtil.toBean(dBInfoMap, DbInfo.class);

            if (ObjUtil.isEmpty(dBInfoMap)){
                throw new MetaCompareException("无对应的数据源。" + JSONUtil.toJsonStr(map));
            }
            SQLDialectEnum sqlDialectEnum = SQLDialectEnum.Default();
            dataSourceProperty.setPoolName(poolName);
            dataSourceProperty.setUrl(sqlDialectEnum.getUrlTemplate(dbInfo.getDatabaseHost(),
                                    dbInfo.getDatabasePort(),
                                    dbInfo.getDatabaseName()));
            dataSourceProperty.setUsername(String.valueOf(dbInfo.getDatabaseUserName()));
            dataSourceProperty.setPassword(dbInfo.getDatabasePassword());
            dataSourceProperty.setDriverClassName(sqlDialectEnum.getDriveClassName());

            // 向容器注册数据源：
            return new Executor(poolName,dynamicDatasourceUtil.addDataSources(dataSourceProperty));
        }
        return new Executor(poolName,dataSources.get(poolName));
    }

    /**
     * 获取执行器（默认master）
     * @return
     */
    private Executor setDataSourceWithExecutor()  {
        return setDataSourceWithExecutor(JDBCUtil.MASTER_POOL_NAME);
    }

    private void recordErrorLog(String errorSql,Object... params){
        // 记录日志
        StringBuilder paramSb = new StringBuilder();
        this.executeWithClose(executor -> {
            for (Object param : params) {
                if (param==null){
                    paramSb.append("null").append(" : ").append("null").append(" | ");
                }else {
                    paramSb.append(param.getClass().getSimpleName()).append(" : ").append(param).append(" | ");
                }
            }
            String errorSqlInfo = StrUtil.format("【{}】,【{}】",errorSql,paramSb.length()>3?paramSb.substring(0, paramSb.length() - 3):"");
            return executor.execute("INSERT IGNORE INTO `temp_jdbc_error_log` (`logrecord`) VALUES (?)",errorSqlInfo);
        });
    }

    /**
     * 封装执行方法，使用try-with-resource，自动关闭连接，统一catch异常
     * @param poolName 数据源
     * @param function 执行的函数
     * @param <R> 函数返回值
     * @return
     */
    public <R> R executeWithClose(String poolName, Function<Executor,R> function) {
        try(Executor executor = this.setDataSourceWithExecutor(poolName);){
            return function.apply(executor);
        }catch (IOException e){
            log.error("数据库连接关闭失败",e);
            throw new MetaCompareException("数据库连接关闭失败");
        }
    }
    public void executeWithClose(String poolName, Consumer<Executor> consumer) {
        executeWithClose(poolName, executor -> {
            consumer.accept(executor);
            return null;
        });
    }
    /**
     * master数据源的封装执行方法，使用try-with-resource，自动关闭连接，统一catch异常
     * @param function 执行的函数
     * @param <R> 函数返回值
     * @return
     */
    public <R> R executeWithClose(Function<Executor,R> function) {
        return executeWithClose(JDBCUtil.MASTER_POOL_NAME,function);
    }


    /**
     * 引擎2、3执行器
     */
    public class Executor implements Closeable {
        /**是否是debug模式（阻止执行execute相关方法）*/
        private boolean debugFlag = false;
        /**数据源名*/
        private final String poolName;// 数据源
        /**数据源连接*/
        private final DataSource dataSource;//
        /**执行链接*/
        private Connection conn;

        public Executor(String poolName,DataSource dataSource) {
            this.poolName = poolName;
            this.dataSource = dataSource;
            try {
                conn = this.dataSource.getConnection();
            } catch (SQLException e) {
                log.error("获取数据库连接失败",e);
            }
        }

        /** 开启debug模式 */
        public void debugMode() {
            System.out.println(ConsoleColorTextBuilder.with("jdbcUtils debug模式启动").red().build());
            this.debugFlag = true;
        }

        /**
         * 执行非查询语句（通用）
         * @param sql sql语句(参数占位符：?)
         * @param params  参数列表
         * @return 受影响行数
         */
        // @Transactional(rollbackFor = Exception.class,propagation = Propagation.SUPPORTS)
        public int execute(String sql,Object... params){
            int count;
            if (!debugFlag){
                try {
                    count = SqlExecutor.execute(this.conn, sql, params);
                } catch (SQLException e) {
                    // 回滚事务（回滚当前线程的事务）
//                    if (TransactionSynchronizationManager.isActualTransactionActive()){
//                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
//                    }
                    // 记录debug日志：
                    recordErrorLog(sql, params);
                    log.error("执行SQL错误",e);
                    throw new MetaCompareException("执行SQL错误，错误信息：\n"+e.getMessage()+"错误sql：\n" + sql+"\n参数："+Arrays.toString(params), e);
                }
            }else {
                count = 1;// 假数据
            }
            log.info("sql执行成功：{}\n{}\n{}",
                    ConsoleColorTextBuilder.with(this.poolName).red().build(),
                    ConsoleColorTextBuilder.with(sql).purple().build(),
                    ConsoleColorTextBuilder.with(Arrays.toString(params)).cyan().build());
            return count;
        }
        // @Transactional(rollbackFor = Exception.class,propagation = Propagation.SUPPORTS)
        public int execute(SqlModel sqlModel){
            return execute(sqlModel.getSql(),sqlModel.getParams().toArray());
        }

        /**
         * 执行查询语句（通用）
         * @param <T> 实体
         * @param sql select语句(参数占位符：?)
         * @param params 参数列表
         * @param elementBeanType 返回的实体类型
         */
        // @Transactional(rollbackFor = Exception.class,propagation = Propagation.SUPPORTS)
        public <T> List<T> query(String sql,Class<T> elementBeanType,Object... params){
            List<T> entities;
            try {
                entities = SqlExecutor.query(this.conn, sql, rs -> HandleHelper.handleRsToBeanList(rs, new ArrayList<>(), elementBeanType), params);
            } catch (SQLException e) {
                // 回滚事务（回滚当前线程的事务，当前线程一般指的是master）
                if (TransactionSynchronizationManager.isActualTransactionActive()){
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                }
                log.error("查询SQL错误",e);
                throw new MetaCompareException("查询SQL错误，错误信息：\n"+e.getMessage()+"错误sql：\n" + sql +"\n参数："+Arrays.toString(params));
            }
            log.info("sql查询成功：{}\n{}\n{}\n{}",
                    ConsoleColorTextBuilder.with(this.poolName).red().build(),
                    ConsoleColorTextBuilder.with(sql).purple().build(),
                    ConsoleColorTextBuilder.with(Arrays.toString(params)).cyan().build(),
                    ConsoleColorTextBuilder.with(JSONUtil.toJsonStr(entities)).blue().build());
            return entities;
        }
        /**
         * 执行sqlModel查询
         */
        // @Transactional(rollbackFor = Exception.class,propagation = Propagation.SUPPORTS)
        public LinkedHashMap<String,Object> queryOneData(SqlModel sqlModel){
            List<LinkedHashMap<String, Object>> entitys = this.queryData(sqlModel);
            if (ObjUtil.isNotEmpty(entitys)){
                return entitys.get(0);
            }
            return new LinkedHashMap<>();
        }

        /**
         * 执行sqlModel查询
         */
        // @Transactional(rollbackFor = Exception.class,propagation = Propagation.SUPPORTS)
        public List<LinkedHashMap<String,Object>> queryData(SqlModel sqlModel){
            String sql = sqlModel.getSql();
            Object[] params = sqlModel.getParams().toArray();
            List<LinkedHashMap> entities;
            try {
                entities = SqlExecutor.query(this.conn,sql,rs -> HandleHelper.handleRsToBeanList(rs, new ArrayList<>(), LinkedHashMap.class), params);
            } catch (SQLException e) {
                // 回滚事务（回滚当前线程的事务，当前线程一般指的是master）
                if (TransactionSynchronizationManager.isActualTransactionActive()){
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                }
                log.error("查询SQL执行错误",e);
                throw new MetaCompareException("查询SQL错误，错误信息：\n"+e.getMessage()+"错误sql：\n" + sql+"\n参数："+Arrays.toString(params));
            }
            log.info("sql查询成功：{}\n{}\n{}\n{}",
                    ConsoleColorTextBuilder.with(this.poolName).red().build(),
                    ConsoleColorTextBuilder.with(sql).purple().build(),
                    ConsoleColorTextBuilder.with(Arrays.toString(params)).cyan().build(),
                    ConsoleColorTextBuilder.with(JSONUtil.toJsonStr(entities)).blue().build());
            // 转成List<LinkedHashMap<String,Object>>类型输出：
            Function<List<LinkedHashMap>,List<LinkedHashMap<String,Object>>> convert = es -> {
                List<LinkedHashMap<String,Object>> entityList = new ArrayList<>(es.size());
                es.forEach(e->{
                    LinkedHashMap<String, Object> linkedHashMap = new LinkedHashMap(e);
                    entityList.add(linkedHashMap);
                });
                return entityList;
            };
            return convert.apply(entities);
        }

        /**
         * 执行sqlModel查询
         * @param <T> 实体，仅限简单类型
         * @return
         */
        // @Transactional(rollbackFor = Exception.class,propagation = Propagation.SUPPORTS)
        public <T> T queryValuesOne(SqlModel sqlModel, Class<T> elementBeanType){
            List<LinkedHashMap<String, Object>> entities = this.queryData(sqlModel);
            Optional<Object> any = entities.get(0).values().stream().findAny();
            return any.map(o -> Convert.convert(elementBeanType, o)).orElse(null);
        }

        /**
         * 开启事务（开启事务后在方法结束后要手动commit，不需要主动执行rollBack，别忘了close）
         */
        public void beginTransaction() {
            try {
                conn.setAutoCommit(false);
            } catch (SQLException e) {
                throw new MetaCompareException(e.getMessage());
            }
        }
        /**
         * 事务回滚
         */
        public void quietRollback() {
            try {
                this.conn.rollback();
            } catch (Exception e) {
                throw new MetaCompareException(e.getMessage());
            } finally {
                try {
                    this.conn.setAutoCommit(true);
                } catch (SQLException e) {
                    throw new MetaCompareException(e.getMessage());
                }
            }
        }
        /**
         * 事务提交
         */
        public void commit() {
            try {
                this.conn.commit();
            } catch (SQLException e){
                throw new MetaCompareException(e.getMessage());
            }finally {
                try {
                    this.conn.setAutoCommit(true);
                } catch (SQLException e) {
                    throw new MetaCompareException(e.getMessage());
                }
            }
        }

        /**
         * 以事务方式执行（不关闭连接）
         * @param function
         * @param <R>
         * @return
         */
        public <R> R transactionExecute(Function<Executor,R> function) {
            this.beginTransaction();
            try {
                R ret = function.apply(this);
                this.commit();
                return ret;
            } catch (Exception e) {
                this.quietRollback();
                throw new RuntimeException(e);
            }
        }
        /**
         * 以事务方式执行（关闭连接！！！）
         * @param function
         * @param <R>
         * @return
         */
        public <R> R transactionExecuteWithClose(Function<Executor,R> function) {
            this.beginTransaction();
            try {
                R ret = function.apply(this);
                this.commit();
                return ret;
            } catch (Exception e) {
                this.quietRollback();
                throw new RuntimeException(e);
            }finally {
                try {
                    this.close();
                } catch (IOException e) {
                    log.error("关闭连接失败",e);
                }
            }
        }


        /**
         * 关闭连接
         */
        @Override
        public void close() throws IOException {
            try {
                /*Class<? extends DataSource> clazz = dataSource.getClass();
                try {
                    Method closeMethod = ReflectionUtils.findMethod(clazz, "close");
                    if (closeMethod != null) {
                        closeMethod.invoke(dataSource);
                    }
                } catch (IllegalAccessException | InvocationTargetException e) {
                    log.warn("close the datasource failed.");
                }*/
                if (this.conn!=null){
                    this.conn.close();
                }
            } catch (SQLException e) {
                throw new MetaCompareException(e.getMessage());
            }
        }
    }

}








